﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

using permit.App_Code.Entity;

namespace permit.App_Code.DataAccess
{
    public class PermitAccess
    {

        string strConn = ConfigurationManager.ConnectionStrings["permitConnectionString"].ConnectionString;
        SqlConnection conn;

        public PermitAccess()
        {

        }
        public SqlConnection GetConnetcion(string connString)
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();
            }
            return conn;
        }

        #region
        /// <summary>
        /// 从数据库中查询数据的,返回单条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PermitTable GetPermit(int perID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT per_ID, per_Department, per_Representative, per_Address, per_Type, per_Mark, per_PermitCode, per_Organ, per_PermitDate, per_StarDate, per_EndDate, per_Tel, per_Number, per_Area, dep_AreaCode, per_PerType, per_Scope, per_Count FROM PermitTable where per_ID = @per_ID and per_Flag=0";

            command.Parameters.Add("@per_ID", SqlDbType.Int, 4, "per_ID").Value = perID;

            command.Connection = GetConnetcion(strConn);

            try
            {
                SqlDataReader reader = command.ExecuteReader();

                PermitTable permit = new PermitTable();

                while (reader.Read())
                {
                    if (reader["per_PermitCode"].ToString() != "")
                    {
                        permit.Per_PermitCode = reader["per_PermitCode"].ToString(); permit.Per_Organ = reader["per_Organ"].ToString();

                    }
                    if (reader["per_PermitDate"].ToString() != "")
                    {
                        permit.PermitDate = Convert.ToDateTime(reader["per_PermitDate"]);
                        permit.Per_StarDate = Convert.ToDateTime(reader["per_StarDate"]);
                        permit.Per_EndDate = Convert.ToDateTime(reader["per_EndDate"]);
                    }
                    permit.Per_ID = Convert.ToInt32(reader["Per_ID"].ToString());
                    permit.Per_Department = reader["per_Department"].ToString();
                    permit.Per_Representative = reader["per_Representative"].ToString();
                    permit.Per_Address = reader["per_Address"].ToString();
                    permit.Per_Mark = reader["per_Mark"].ToString();
                    permit.Per_Type = reader["per_Type"].ToString();
                    permit.Per_Tel = reader["per_Tel"].ToString();
                    permit.Per_AreaCode = reader["dep_AreaCode"].ToString();
                    permit.Per_PerType = reader["per_PerType"].ToString();
                    permit.Per_Scope = reader["per_Scope"].ToString();
                    permit.Per_Count = Convert.ToInt32(reader["per_Count"].ToString());

                    if (reader["per_Number"] == DBNull.Value)
                    {
                        permit.Per_Number = 0;
                    }
                    else
                    {
                        permit.Per_Number = Convert.ToInt32(reader["per_Number"]);
                    }
                    if (reader["per_Area"] == DBNull.Value)
                    {
                        permit.Per_Area = 0.0;
                    }
                    else
                    {
                        permit.Per_Area = double.Parse(reader["per_Area"].ToString());
                    }


                }
                reader.Dispose();
                connClose();//关闭连接

                return permit;//返回结果
            }

            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        //public int IsPermitCode(string perCode)
        //{
        //    SqlCommand command = new SqlCommand();

        //    command.CommandText = "select * from PermitTable where per_PermitCode = @per_PermitCode";

        //    command.Connection = GetConnetcion(strConn);

        //    command.Parameters.Add("@per_PermitCode", SqlDbType.VarChar, 50, "per_PermitCode").Value = perCode;
          
        //    try
        //    {
        //        SqlDataReader reader = command.ExecuteReader();
        //        int count = 0;
        //        while (reader.Read())
        //        {
        //            count++;
        //            break;
        //        }
        //        connClose();//关闭连接
        //        return count;
        //    }
        //    catch (Exception ex)
        //    {
        //        return 0;
        //    }

        //}

        #region
        /// <summary>
        /// 从数据库中查询数据的,返回单条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PermitTable GetPermit(string perCode)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT per_ID, per_Department, per_Representative, per_Address, per_Type, per_Mark, per_PermitCode, per_Organ, per_PermitDate, per_StarDate, per_EndDate FROM PermitTable where per_PermitCode = @per_PermitCode and per_Flag=0";

            command.Parameters.Add("@per_PermitCode", SqlDbType.VarChar, 50, "per_PermitCode").Value = perCode;

            command.Connection = GetConnetcion(strConn);

            try
            {
                SqlDataReader reader = command.ExecuteReader();

                PermitTable permit = new PermitTable();

                while (reader.Read())
                {

                    permit.Per_ID = Convert.ToInt32(reader["Per_ID"].ToString());
                    permit.Per_Department = reader["per_Department"].ToString();
                    permit.Per_Representative = reader["per_Representative"].ToString();
                    permit.Per_Address = reader["per_Address"].ToString();
                    permit.Per_Mark = reader["per_Mark"].ToString();
                    permit.Per_PermitCode = reader["per_PermitCode"].ToString();
                    permit.PermitDate = Convert.ToDateTime(reader["per_PermitDate"]);
                    permit.Per_StarDate = Convert.ToDateTime(reader["per_StarDate"]);
                    permit.Per_EndDate = Convert.ToDateTime(reader["per_EndDate"]);
                    permit.Per_Organ = reader["per_Organ"].ToString();
                    permit.Per_Type = reader["per_Type"].ToString();

                }
                reader.Dispose();
                connClose();//关闭连接

                return permit;//返回结果
            }

            catch (Exception ex)
            {
                return null;
            }




        }
        #endregion

        #region
        /// <summary>
        /// 根据多个查询条件，得到结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetPermits(PermitTable permit)
        {
            SqlCommand command = new SqlCommand();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * From PermitTable Where 1=1 ");
            if (permit.Per_Department != "")
            {
                sb.Append(" And per_Department=@per_Department");
            }
            if (permit.Per_Address != "")
            {
                sb.Append(" And per_Address=@per_Address");
            }
            if (permit.Per_Type != "")
            {
                sb.Append(" And per_Type=@per_Type");
            }
            if (permit.Per_PermitCode != "")
            {
                sb.Append(" And per_PermitCode=@per_PermitCode");
            }
            if (permit.Per_Organ != "")
            {
                sb.Append(" And per_Organ=@per_Organ");
            }
            if (permit.PermitDate != DateTime.MinValue)
            {
                sb.Append(" And per_PermitDate=@per_PermitDate");
                command.Parameters.Add("@per_PermitDate", SqlDbType.DateTime, 8, "per_PermitDate").Value = permit.PermitDate;
            }
            if (permit.Per_StarDate != DateTime.MinValue)
            {
                sb.Append(" And per_StarDate=@per_StarDate");
                command.Parameters.Add("@per_StarDate", SqlDbType.DateTime, 8, "per_StarDate").Value = permit.Per_StarDate;
            }
            if (permit.Per_EndDate != DateTime.MinValue)
            {
                sb.Append(" And per_EndDate=@per_EndDate");
                command.Parameters.Add("@per_EndDate", SqlDbType.DateTime, 8, "per_EndDate").Value = permit.Per_EndDate;
            }
            if (permit.Per_Representative != "")
            {
                sb.Append(" And per_Representative=@per_Representative"); //per_Flag 0表示存在， 1表示逻辑删除
            }          
            if (permit.Per_Number > 0)
            {
                sb.Append(" And per_Number=@per_Number");
            }
            if (permit.Per_Area != 0.0)
            {
                sb.Append(" And per_Area=@per_Area");
            }
            if (permit.Per_AreaCode != "")
            {
                sb.Append(" And dep_AreaCode=@dep_AreaCode");
            }

            if (permit.Per_Count > -1)
            {
                if (permit.Per_Count == 0)
                {
                    sb.Append(" And per_Count > 0");
                    sb.Append(" And per_Flag=0 ORDER BY per_PermitCode DESC");
                }
                if (permit.Per_Count == 1)
                {
                    sb.Append(" And per_Count = 0");
                    sb.Append(" And per_Flag=0 ORDER BY per_InputDate DESC");
                }

            }
            

            command.CommandText = sb.ToString();

            command.Parameters.Add("@per_Department", SqlDbType.VarChar, 50, "per_Department").Value = permit.Per_Department;
            command.Parameters.Add("@per_Address", SqlDbType.VarChar, 50, "per_Address").Value = permit.Per_Address;
            command.Parameters.Add("@per_Type", SqlDbType.VarChar, 50, "per_Type").Value = permit.Per_Type;
            command.Parameters.Add("@per_PermitCode", SqlDbType.VarChar, 50, "per_PermitCode").Value = permit.Per_PermitCode;
            command.Parameters.Add("@per_Organ", SqlDbType.VarChar, 50, "per_Organ").Value = permit.Per_Organ;
            command.Parameters.Add("@per_Representative", SqlDbType.VarChar, 50, "per_Representative").Value = permit.Per_Representative;

            command.Parameters.Add("@per_Area", SqlDbType.Float, 8, "per_Area").Value = permit.Per_Area;
            command.Parameters.Add("@per_Number", SqlDbType.Int, 4, "per_Number").Value = permit.Per_Number;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = permit.Per_AreaCode;

            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter ad = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {

                ad.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }

            ds.Dispose();
            ad.Dispose();
            return ds;


        }

        #endregion


        /// <summary>
        /// 插入数据，影响返回行数
        /// </summary>
        /// <param name="permit"></param>
        /// <returns></returns>
        public int InsertPermit(PermitTable permit)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "INSERT INTO PermitTable(per_Department, per_Representative, per_Address,per_Type, per_Mark, per_Tel, per_Number, per_Area, per_Flag, per_Count, per_InputDate, per_InputPersion, dep_AreaCode, per_Scope)VALUES (@per_Department,@per_Representative,@per_Address,@per_Type,@per_Mark,@per_Tel, @per_Number, @per_Area,@per_Flag,@per_Count,@per_InputDate,@per_InputPersion,@dep_AreaCode,@per_Scope)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@per_Department", SqlDbType.VarChar, 50, "per_Department").Value = permit.Per_Department;
            command.Parameters.Add("@per_Representative", SqlDbType.VarChar, 50, "per_Representative").Value = permit.Per_Representative;
            command.Parameters.Add("@per_Address", SqlDbType.VarChar, 50, "per_Address").Value = permit.Per_Address;
            command.Parameters.Add("@per_Type", SqlDbType.VarChar, 50, "per_Type").Value = permit.Per_Type;
            if (permit.Per_Mark == "")
            {
                command.Parameters.Add("@per_Mark", SqlDbType.VarChar, 50, "per_Mark").Value = DBNull.Value;
            }
            else
            {
                command.Parameters.Add("@per_Mark", SqlDbType.VarChar, 50, "per_Mark").Value = permit.Per_Mark;
            }

            command.Parameters.Add("@per_Tel", SqlDbType.VarChar, 50, "per_Tel").Value = permit.Per_Tel;
            command.Parameters.Add("@per_Number", SqlDbType.Int, 4, "per_Number").Value = permit.Per_Number;
            command.Parameters.Add("@per_Area", SqlDbType.Float, 8, "per_Area").Value = permit.Per_Area;
            command.Parameters.Add("@per_Flag", SqlDbType.TinyInt, 1, "per_Flag").Value = permit.Per_Flag;
            command.Parameters.Add("@per_Count", SqlDbType.Int, 4, "per_Count").Value = permit.Per_Count;
            command.Parameters.Add("@per_InputDate", SqlDbType.DateTime, 8, "per_InputDate").Value = permit.Per_InputDate;
            command.Parameters.Add("@per_InputPersion", SqlDbType.VarChar, 50, "per_InputPersion").Value = permit.Per_InputPersion;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = permit.Per_AreaCode;
            command.Parameters.Add("@per_Scope", SqlDbType.VarChar, 50, "per_Scope").Value = permit.Per_Scope;
            try
            {
                int count = command.ExecuteNonQuery();
                connClose();//关闭连接
                return count;
            }
            catch (Exception ex)
            {
                return 0;
            }


        }



        #region
        /// <summary>
        /// 更新一条记录,通过PermitTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int UpdatePermit(PermitTable permit)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE PermitTable SET per_Department = @per_Department, per_Representative = @per_Representative, per_Address = @per_Address, per_Type = @per_Type, per_Mark = @per_Mark, per_PermitCode = @per_PermitCode, per_Organ = @per_Organ, per_PermitDate = @per_PermitDate, per_StarDate = @per_StarDate, per_EndDate = @per_EndDate, per_Flag = @per_Flag,per_InputDate = @per_InputDate, per_InputPersion = @per_InputPersion, per_Area = @per_Area, per_Tel = @per_Tel, per_Number = @per_Number, per_PerType = @per_PerType, per_Scope = @per_Scope WHERE (per_ID = @per_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@per_Department", SqlDbType.VarChar, 50, "per_Department").Value = permit.Per_Department;
            command.Parameters.Add("@per_Representative", SqlDbType.VarChar, 50, "per_Representative").Value = permit.Per_Representative;
            command.Parameters.Add("@per_Address", SqlDbType.VarChar, 50, "per_Address").Value = permit.Per_Address;
            command.Parameters.Add("@per_Type", SqlDbType.VarChar, 50, "per_Type").Value = permit.Per_Type;
            if (permit.Per_Mark == "")
            {
                command.Parameters.Add("@per_Mark", SqlDbType.VarChar, 50, "per_Mark").Value = DBNull.Value;
            }
            else
            {
                command.Parameters.Add("@per_Mark", SqlDbType.VarChar, 50, "per_Mark").Value = permit.Per_Mark;
            }

            command.Parameters.Add("@per_PermitCode", SqlDbType.VarChar, 50, "per_PermitCode").Value = permit.Per_PermitCode;
            command.Parameters.Add("@per_Organ", SqlDbType.VarChar, 50, "per_Organ").Value = permit.Per_Organ;
            if (permit.PermitDate != DateTime.MinValue)
            {
                command.Parameters.Add("@per_PermitDate", SqlDbType.DateTime, 8, "per_PermitDate").Value = permit.PermitDate;
                command.Parameters.Add("@per_StarDate", SqlDbType.DateTime, 8, "per_StarDate").Value = permit.Per_StarDate;
                command.Parameters.Add("@per_EndDate", SqlDbType.DateTime, 8, "per_EndDate").Value = permit.Per_EndDate;
            }
            else
            {
                command.Parameters.Add("@per_PermitDate", SqlDbType.DateTime, 8, "per_PermitDate").Value = DBNull.Value;
                command.Parameters.Add("@per_StarDate", SqlDbType.DateTime, 8, "per_StarDate").Value = DBNull.Value;
                command.Parameters.Add("@per_EndDate", SqlDbType.DateTime, 8, "per_EndDate").Value = DBNull.Value;
            }
            command.Parameters.Add("@per_Flag", SqlDbType.TinyInt, 1, "per_Flag").Value = permit.Per_Flag;
            //command.Parameters.Add("@per_Count", SqlDbType.Int, 4, "per_Count").Value = permit.Per_Count;
            command.Parameters.Add("@per_InputDate", SqlDbType.DateTime, 8, "per_InputDate").Value = permit.Per_InputDate;
            command.Parameters.Add("@per_InputPersion", SqlDbType.VarChar, 50, "per_InputPersion").Value = permit.Per_InputPersion;
            command.Parameters.Add("@per_Area", SqlDbType.Decimal, 9, "per_Area").Value = permit.Per_Area;
            command.Parameters.Add("@per_Tel", SqlDbType.VarChar, 50, "per_Tel").Value = permit.Per_Tel;
            command.Parameters.Add("@per_Number", SqlDbType.Int, 4, "per_Number").Value = permit.Per_Number;

            command.Parameters.Add("@per_ID", SqlDbType.Int, 4, "per_ID").Value = permit.Per_ID;
            command.Parameters.Add("@per_PerType", SqlDbType.VarChar, 50, "per_PerType").Value = permit.Per_PerType;
            command.Parameters.Add("@per_Scope", SqlDbType.VarChar, 50, "per_Scope").Value = permit.Per_Scope;

            int count;
            try
            {
                count = command.ExecuteNonQuery();


                connClose();//关闭连接
                return count;//返回结果
            }
            catch(Exception ex)
            {
                return -1;
            }

            


        }
        #endregion


        public int UpdatePermitInfo(PermitTable permit)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE PermitTable SET per_Count = per_Count + @per_Count,per_Printer = @per_Printer WHERE (per_ID = @per_ID and per_Flag = 0) ";

            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@per_Count", SqlDbType.Int, 4, "per_Count").Value = permit.Per_Count;
            command.Parameters.Add("@per_Printer", SqlDbType.VarChar, 50, "per_Printer").Value = permit.Per_Printer;
            command.Parameters.Add("@per_ID", SqlDbType.Int, 4, "per_ID").Value = permit.Per_ID;

            int count;
            count = command.ExecuteNonQuery();


            connClose();//关闭连接

            return count;//返回结果


        }
        #region
        /// <summary>
        /// 更新一条记录,per_Flag 变成1，逻辑删除
        /// </summary>
        /// <param name="per"></param>
        /// <returns>count</returns>
        public int UpdatePermit(int perID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE PermitTable SET per_Flag=1 WHERE (per_ID = @per_ID)";

            command.Connection = GetConnetcion(strConn);


            command.Parameters.Add("@per_ID", SqlDbType.Int, 4, "per_ID").Value = perID;

            int count;
            count = command.ExecuteNonQuery();


            connClose();//关闭连接

            return count;//返回结果


        }
        #endregion


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void connClose()
        {
            if (conn.State == ConnectionState.Open)
            {//判断数据库的连接状态，如果状态是打开的话就将它关闭

                conn.Close();
            }
        }


        public int GetPermitCount(string areaCode, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = @"SELECT COUNT(*) FROM PermitTable WHERE (dep_AreaCode = @dep_AreaCode) AND(per_PermitCode IS NOT NULL) AND (per_Flag = 0) AND (YEAR(per_PermitDate) = @per_PermitDate)";



            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = areaCode;
            command.Parameters.Add("@per_PermitDate", SqlDbType.Int, 4, "per_PermitDate").Value = year;

            command.Connection = GetConnetcion(strConn);

            int count = 0;
            try
            {

                count = Convert.ToInt32(command.ExecuteScalar());


                connClose();//关闭连接

                return count;//返回结果
            }

            catch (Exception ex)
            {
                return -1;
            }
        }


        public DataSet GetPollPermits(string areaCode, int perYear, int perMonth)
        {

            SqlCommand command = new SqlCommand();

            command.CommandText = @"SELECT per_PermitCode, per_Department,per_Address,per_Representative,per_Tel, per_Area,per_Type,per_Number, per_PermitDate, per_EndDate, per_Mark, per_Scope FROM PermitTable WHERE (MONTH(per_PermitDate) = @per_permitM) AND (YEAR(per_PermitDate) = @per_perY) AND (per_Count > 0) AND (per_Flag = 0) AND(dep_AreaCode = @dep_AreaCode) ORDER BY per_PermitCode";

            command.Parameters.Add("@per_permitM", SqlDbType.Int, 4, "per_PermitDate").Value = perMonth;
            command.Parameters.Add("@per_perY", SqlDbType.Int, 4, "per_PermitDate").Value = perYear;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = areaCode;

            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter ad = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {

                ad.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }

            ds.Dispose();
            ad.Dispose();
            return ds;



        }

    }
}
